package com.demo.common.utils;

import com.demo.common.annotation.InExcelAndSort;
import com.demo.common.constants.ResponseCode;
import com.demo.common.exception.ServiceException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;

@Component
public class ExcelUtil {

    private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);


    /**
     * 创建工作薄
     *
     * @return
     */
    public SXSSFSheet createSheet(SXSSFWorkbook workbook, String sheetName, String[] titles) {
        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth(20);
        CellStyle cellStyle = setCellStyle(workbook);

        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(titles[i]);
        }
        return sheet;
    }

    public CellStyle setCellStyle(SXSSFWorkbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        // 设置这些样式
        //        cellStyle.setFillForegroundColor(XSSFCellStyle.SOLID_FOREGROUND);
        //        cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 生成一个字体
        Font font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setColor(IndexedColors.BLACK.getIndex());
        font.setFontHeightInPoints((short) 11);
        // 把字体应用到当前的样式
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置头信息
     *
     * @param request
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void setHeadInfo(HttpServletRequest request, HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        String userAgent = request.getHeader("User-Agent");
        // 针对IE或者以IE为内核的浏览器：
        if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
            response.setHeader("Content-disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
        } else {
            // 非IE浏览器的处理：
            response.setHeader("Content-disposition",
                    "attachment;filename=" + new String(fileName.getBytes(), "iso-8859-1") + ".xlsx");
        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
    }

    /**
     * @param excelVO （表格的标题）
     * @throws Exception
     * @Title: exportExcel
     * @Description: 导出Excel的方法
     * @author: yitao.wang
     */
    public static <T> void exportExcel(HttpServletRequest request, HttpServletResponse response,
                                       ExcelVO excelVO, List<T> list) throws IOException {
        OutputStream outPutStream = response.getOutputStream();
        setHeadInfo(request, response, excelVO.getFileName());
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 生成一个表格
        SXSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "sheet1");
        // 生成标题及设置标题样式
        SXSSFRow row = sheet.createRow(0);
        List<Column> columnList = excelVO.getColumnList();
        List<String> propertyList = new ArrayList<>(32);
        for (int i = 0; i < columnList.size(); i++) {
            Column column = columnList.get(i);
            sheet.setColumnWidth(i, column.getCellWidth());
            SXSSFCell cell = row.createCell((short) i);
            cell.setCellStyle(setHeaderStyle(workbook));
            HSSFRichTextString text = new HSSFRichTextString(column.getColumnName());
            cell.setCellValue(text.toString());
            propertyList.add(column.getPropertyName());
        }
        Map<String, Object> valueMap;
        Column column;
        // 遍历集合数据，产生数据行
        if (list.size() > 0) {
            if (list.size() > excelVO.getMaxRowNumber()) {
                row = sheet.createRow(1);
                SXSSFCell cell = row.createCell((short) 0);
                cell.setCellValue("数据量不能超过" + excelVO.getMaxRowNumber() + "条,请缩小查询范围!");
            } else {
                int index = 1;
                for (T obj : list) {
                    row = sheet.createRow(index);
                    int cellIndex = 0;
                    valueMap = setValueMap(obj, propertyList);
                    for (int i = 0; i < columnList.size(); i++) {
                        column = columnList.get(i);
                        SXSSFCell cell = row.createCell((short) cellIndex);
                        setCellValue(cell, valueMap, column);
                        column = null;
                        cellIndex += 1;
                    }
                    valueMap = null;
                    index += 1;
                }
                list.clear();
            }
        } else {
            row = sheet.createRow(1);
            SXSSFCell cell = row.createCell(0);
            cell.setCellValue("无数据!");
        }
        workbook.write(outPutStream);
        outPutStream.flush();
        outPutStream.close();
    }

    /**
     * 给单元格设置值
     *
     * @param cell
     * @param valueMap
     * @param column
     */
    private static void setCellValue(SXSSFCell cell, Map<String, Object> valueMap, Column column) {
        Object value = valueMap.get(column.getPropertyName());
        if (value instanceof Double) {
            cell.setCellValue((Double) value);
        } else if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Integer) {
            cell.setCellValue((Integer) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else if (value instanceof Date) {
            cell.setCellValue((Date) value);
        } else if (value instanceof RichTextString) {
            cell.setCellValue((RichTextString) value);
        } else if (value instanceof Calendar) {
            cell.setCellValue((Calendar) value);
        }//使用了BigDecimal
        else if (value instanceof BigDecimal) {
            cell.setCellValue(((BigDecimal) value).doubleValue());
        }
    }

    /**
     * 反射获取属性对应的值
     *
     * @param obj
     * @param propertyList
     * @param <T>
     * @return
     */
    private static <T> Map<String, Object> setValueMap(T obj, List<String> propertyList) {
        Map<String, Object> valueMap = new HashMap<String, Object>();
        try {
            Class<? extends Object> clazz = obj.getClass();
            for (String valueName : propertyList) {
                //若未自定义赋值，则自动为其赋值
                if (!valueMap.containsKey(valueName)) {
                    //若T为Map则直接根据key获取value，否则通过反射进行获取值
                    if (obj instanceof Map) {
                        Map<String, Object> tMap = (Map<String, Object>) obj;
                        valueMap.put(valueName, tMap.get(valueName));
                    } else {
                        //拼装方法名
                        String methodName = "get" + valueName.substring(0, 1).toUpperCase() + valueName.substring(1);
                        Method m = clazz.getMethod(methodName);
                        m.invoke(obj);
                        valueMap.put(valueName, m.invoke(obj));
                    }
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new ServiceException(ResponseCode.EXCEPTION, "自动封装valueMap异常！");
    }
        return valueMap;
    }

    public static CellStyle setHeaderStyle(SXSSFWorkbook workbook) {
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        //样式
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //字体
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }


    public String checkData(Object param) {
        if (param != null && param != "") {
            if (param instanceof Integer) {
                int value = ((Integer) param).intValue();
                return String.valueOf(value);
            } else if (param instanceof String) {
                String s = (String) param;
                return s;
            } else if (param instanceof Double) {
                double d = ((Double) param).doubleValue();
                return String.valueOf(d);
            } else if (param instanceof Float) {
                float f = ((Float) param).floatValue();
                return Float.toString(f);
            } else if (param instanceof Long) {
                long l = ((Long) param).longValue();
                return Long.toString(l);
            } else if (param instanceof Boolean) {
                boolean b = ((Boolean) param).booleanValue();
                return String.valueOf(b);
            } else if (param instanceof Date) {
                Date d = (Date) param;
                return CalendarUtil.formatDate(d, CalendarUtil.TIME_PATTERN);
            }
        }
        return "";
    }

    /**
     * 下载excel
     *
     * @param response
     * @param fileName
     * @throws IOException
     */
    public void doOutPrint(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook workbook, String fileName) {
        OutputStream os = null;
        try {
            fileName = fileName.concat(".xlsx");
            String userAgent = request.getHeader("User-Agent");

            // 针对IE或者以IE为内核的浏览器：
            if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            } else {
                // 非IE浏览器的处理：
                fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
            }
            response.setHeader("Content-disposition", String.format("attachment; filename=\"%s\"", fileName));
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");

            os = response.getOutputStream();
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                os.flush();
                os.close();
                os = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 生成excel
     *
     * @param fileName
     * @throws IOException
     */
    public void doOutPrint(SXSSFWorkbook workbook, String fileName, String path) {
        FileOutputStream os = null;
        try {
            fileName = fileName.concat(".xlsx");
            fileName = path + "/" + fileName;
            os = new FileOutputStream(fileName);
            workbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                os.flush();
                if (os != null) {
                    os.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }


    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            //获取excel文件的io流
            InputStream is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007 及2007以上
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }


    /**
     * 将集合中的属性放入sheet
     *
     * @param collection
     * @param sheet
     * @param <T>
     * @throws IllegalAccessException
     */
    public <T> void insertValueIntoExcel(Collection collection, SXSSFSheet sheet) throws IllegalAccessException {

        Class<?> clazz = collection.iterator().next().getClass();
        Field[] fields = clazz.getDeclaredFields();
        int i = 1;
        for (Object o : collection) {
            SXSSFRow row = sheet.createRow(i++);

            for (Field field : fields) {
                InExcelAndSort annotation = field.getAnnotation(InExcelAndSort.class);
                if (annotation == null) {
                    continue;
                }
                field.setAccessible(true);
                row.createCell(annotation.sort()).setCellValue(checkData(field.get(o)));
            }
        }

    }

    /**
     * 将集合中的属性放入sheet
     *
     * @param collection
     * @param sheet
     * @param rows
     * @throws IllegalAccessException
     */
    public void insertValueIntoExcel(Collection collection, SXSSFSheet sheet, Integer rows) throws IllegalAccessException {

        Class<?> clazz = collection.iterator().next().getClass();
        Field[] fields = clazz.getDeclaredFields();
        for (Object o : collection) {
            SXSSFRow row = sheet.createRow(rows++);

            for (Field field : fields) {
                InExcelAndSort annotation = field.getAnnotation(InExcelAndSort.class);
                if (annotation == null) {
                    continue;
                }
                field.setAccessible(true);
                row.createCell(annotation.sort()).setCellValue(checkData(field.get(o)));
            }
        }

    }
}
